﻿using Aspfm.Main;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Aspfm.Dal
{
    class SqlServerDB : IDbHelper
    {
        public SqlServerDB(string conn)
        {
            connstr = conn;
        }
        public override DataSourceType GetDbType()
        {
            return DataSourceType.MSSql;
        }
        internal override IDbConnection GetNewConn()
        {
            return new SqlConnection(connstr);
        }

        internal override IDbDataAdapter GetDataAdapter()
        {
            return new SqlDataAdapter();
        }
        internal override IDataParameter ParamInit(string paramName, object value, string colType = "VARCHAR2", ParameterDirection paramDir = ParameterDirection.Input, int length = 200)
        {
            SqlParameter iparam = new SqlParameter();
            if (paramDir != ParameterDirection.Input)
            {
                iparam.Size = length;
            }
            iparam.Direction = paramDir;
            iparam.ParameterName = paramName;
            iparam.Value = value;
            iparam.SqlDbType = GetDataTypeByType(colType);
            if (iparam.Value != null)
            {
                iparam.Value = CheckValueType(colType, value, paramName);
            }
            if (iparam.Value == null) iparam.Value = "";
            return iparam;
        }
        public SqlDbType GetDataTypeByType(string sqlTypeString = "VARCHAR2")
        {
            SqlDbType dbType = SqlDbType.Variant;//默认为Object
            sqlTypeString = sqlTypeString.Replace(" ", "").Replace("_", "").Replace("2", "").ToLower();
            switch (sqlTypeString)
            {
                case "int":
                    dbType = SqlDbType.Int;
                    break;
                case "varchar":
                    dbType = SqlDbType.VarChar;
                    break;
                case "bit":
                    dbType = SqlDbType.Bit;
                    break;
                case "datetime":
                case "date":
                    dbType = SqlDbType.DateTime;
                    break;
                case "decimal":
                    dbType = SqlDbType.Decimal;
                    break;
                case "float":
                    dbType = SqlDbType.Float;
                    break;
                case "image":
                    dbType = SqlDbType.Image;
                    break;
                case "money":
                    dbType = SqlDbType.Money;
                    break;
                case "ntext":
                    dbType = SqlDbType.NText;
                    break;
                case "nvarchar":
                    dbType = SqlDbType.NVarChar;
                    break;
                case "smalldatetime":
                    dbType = SqlDbType.SmallDateTime;
                    break;
                case "smallint":
                    dbType = SqlDbType.SmallInt;
                    break;
                case "text":
                    dbType = SqlDbType.Text;
                    break;
                case "bigint":
                    dbType = SqlDbType.BigInt;
                    break;
                case "binary":
                    dbType = SqlDbType.Binary;
                    break;
                case "char":
                    dbType = SqlDbType.Char;
                    break;
                case "nchar":
                    dbType = SqlDbType.NChar;
                    break;
                case "numeric":
                    dbType = SqlDbType.Decimal;
                    break;
                case "real":
                    dbType = SqlDbType.Real;
                    break;
                case "smallmoney":
                    dbType = SqlDbType.SmallMoney;
                    break;
                case "sql_variant":
                    dbType = SqlDbType.Variant;
                    break;
                case "timestamp":
                    dbType = SqlDbType.Timestamp;
                    break;
                case "tinyint":
                    dbType = SqlDbType.TinyInt;
                    break;
                case "uniqueidentifier":
                    dbType = SqlDbType.UniqueIdentifier;
                    break;
                case "varbinary":
                    dbType = SqlDbType.VarBinary;
                    break;
                case "xml":
                    dbType = SqlDbType.Xml;
                    break;
                default:
                    throw new Exception("not find sqltype:" + sqlTypeString);
            }
            return dbType;
        }

        internal override DataSet LaodAllColType(string tableName, string columnName = null)
        {
            string sqlstr = "";
            if (tableName.Contains("."))
            {
                string userdbo = tableName.Substring(0, tableName.LastIndexOf('.'));
                tableName = tableName.Substring(tableName.LastIndexOf('.'));
                sqlstr = @"select name column_name,
                (select name from " + userdbo + @"systypes where xusertype=scol.xusertype and xtype=scol.xtype) data_type
                 from " + userdbo + @"syscolumns scol where id = (
                SELECT id FROM " + userdbo + @"SYSOBJECTS where XTYPE IN ('U','V') AND name='" + tableName.Trim().ToUpper() + "')";
            }
            else
            {
                sqlstr = @"select name column_name,
                (select name from systypes where xusertype=scol.xusertype and xtype=scol.xtype) data_type
                 from syscolumns scol where id = (
                SELECT id FROM SYSOBJECTS where XTYPE IN ('U','V') AND name='" + tableName.Trim().ToUpper() + "')";
            }
            if (!string.IsNullOrEmpty(columnName))
            {
                sqlstr += " and name='" + columnName.Trim().ToUpper() + "'";
            }
            return QuerySql(sqlstr);
        }

        public override bool ExistTableOrView(string tableName)
        {
            if (tableName.Contains("."))
            {
                string userdbo = tableName.Substring(0, tableName.LastIndexOf('.'));
                tableName = tableName.Substring(tableName.LastIndexOf('.'));
                return QuerySqlCount("select name from " + userdbo + "sysobjects where XTYPE IN ('U','V') AND name='{0}'", tableName.Trim().ToUpper())>0;
            }
            else
            {
                return QuerySqlCount("select name from sysobjects where XTYPE IN ('U','V') AND name='"+tableName.Trim().ToUpper()+"'")>0;
            }
        }

        public override DataSet LaodProcParamsType(string procName)
        {
            string sqlstr = @"select replace(name,'@','') argument_name,
                    (select name from systypes where xusertype=scol.xusertype and xtype=scol.xtype) data_type,
                    colorder,
                    isoutparam,
                    length
                     from syscolumns scol where id = (
                    SELECT id FROM SYSOBJECTS where XTYPE ='P' AND name='" + procName.Trim().ToUpper()+"') order by colorder";
            return QuerySql(sqlstr);
        }

        internal override string GetParamsName(string procName)
        {
            if (procName.StartsWith("@"))
            {
                return procName;
            }
            return "@p_" + procName;
        }

        public override DataSet LaodProcAll()
        {
            string sqlstr = @"select name pname from syscomments a, sysobjects b where a.id=b.id  and b.xtype='p' order by name";
            return QuerySql(sqlstr);
        }
    }
}
